package com.wms.db.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wms.db.dao.IBaseDao;


@SuppressWarnings("unchecked") 
public class BaseDao extends HibernateDaoSupport implements IBaseDao { 

    // -------------------- HSQL ----------------------------------------------
    // hql for insert update and delete
    public int bulkUpdate(String queryString) {
        return getHibernateTemplate().bulkUpdate(queryString);
    }

    // hql for insert update and delete with parameter
    public int bulkUpdate(String queryString, Object[] values) {
        return getHibernateTemplate().bulkUpdate(queryString, values);
    }

    // hql for query
    public List find(String queryString) {
        return getHibernateTemplate().find(queryString);
    }

    // hql for query with parameters
    public List find(String queryString, Object[] values) {
        return getHibernateTemplate().find(queryString, values);
    }

    // named hql for query with parameters and names
    public List findByNamedParam(String queryString, String[] paramNames, Object[] values) {
        return getHibernateTemplate().findByNamedParam(queryString, paramNames, values);
    }

    // hql for query with pagination
    public List find(final String hql, final int startIndex, final int resultNum) {
        List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List result = session.createQuery(hql).setFirstResult(startIndex).setMaxResults(resultNum).list();
                return result;
            }
        });
        return list;
    }
    // hql for query with pagination and parameters
    public List find(final String hql, final Object value, final int startIndex, final int resultNum) {
        List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                List result = session.createQuery(hql).setParameter(0, value)
                        .setFirstResult(startIndex).setMaxResults(resultNum).list();
                return result;
            }
        });
        return list;
    }
    
    // hql for query with pagination and parameters
    public List find(final String hql, final Object[] values, final int startIndex, final int resultNum) {
        List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
                List result = query.setFirstResult(startIndex).setMaxResults(resultNum).list();
                return result;
            }
        });
        return list;
    }
    
    // find by sql
    public List<Map<String, Object>> executeQuerySQL(final String sql){
    	List<Map<String, Object>> res= new ArrayList<Map<String, Object>>();
    	HibernateCallback callback = new HibernateCallback(){
    		public List doInHibernate(Session session) throws HibernateException, SQLException {
    			Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    			return query.list();
    		}
    	};
    	List temps = (List)this.getHibernateTemplate().execute(callback);
    	if(null != temps){
    		res = temps;
    	}
    	return res;
    }
    
    // call proc
    public void executeProcSQL(final String sql){
    	List<Map<String, Object>> res= new ArrayList<Map<String, Object>>();
    	HibernateCallback callback = new HibernateCallback(){
    		public List doInHibernate(Session session) throws HibernateException, SQLException {
    			Query query = session.createSQLQuery(sql);
    			query.executeUpdate();
    			return new ArrayList();
    		}
    	};
    	this.getHibernateTemplate().execute(callback);
    	
    }
    
    // find by sql
    public List<Map<String, Object>> executeQuerySQL(final String sql, final int startIndex, final int resultNum){
    	List<Map<String, Object>> res= new ArrayList<Map<String, Object>>();
    	HibernateCallback callback = new HibernateCallback(){
    		public List doInHibernate(Session session) throws HibernateException, SQLException {
    			Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    			if(-1 != startIndex && -1 != resultNum){
    				query.setFirstResult(startIndex);
    				query.setMaxResults(resultNum);
    			}
    			return query.list();
    		}
    	};
    	List temps = (List)this.getHibernateTemplate().execute(callback);
    	if(null != temps){
    		res = temps;
    	}
    	return res;
    }

    // -------------------------------- Others -------------------------------- 
    // flush cache data into database only call after update
    public void flush() { 
        getHibernateTemplate().flush(); 
    } 
} 